package com.basic.jxl;

import java.io.FileInputStream;

import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.util.Units;
import org.apache.xmlbeans.XmlCursor;
import org.apache.xmlbeans.XmlObject;

import javax.xml.namespace.QName;

class ReadExcelXSSFControls {

	public ReadExcelXSSFControls() throws Exception {
		XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream("C:\\Users\\15416\\Desktop\\云印客户信息表R1.xlsx"));

		Sheet sheet = wb.getSheetAt(0);
		for (Row row : sheet) {
			for (int c = 0; c < 10; c++) {
				Cell cell = row.getCell(c);
				if (row.getRowNum() == 0) {
					System.out.print(cell + "\t");
				} else {
					if (c == 0) {
						System.out.print(cell + "\t");
					} else if (c == 1) {
						if (cell == null)
							cell = row.createCell(c);
						Control contol = getControlAt((XSSFCell) cell);
						System.out.print(contol);
					}
				}
			}
			System.out.println();
		}

		wb.close();
	}

	private Control getControlAt(XSSFCell cell) throws Exception {
		XSSFSheet sheet = cell.getSheet();
		Row row = cell.getRow();
		int r = 2;
		int c = 5;

		int drheight = (int) Math.round(sheet.getDefaultRowHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
		int rheight = (int) Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI);
		row = null;
		if (r > 0)
			row = sheet.getRow(r - 1);
		int rheightbefore = (row != null) ? (int) Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI) : drheight;
		row = sheet.getRow(r + 1);
		int rheightafter = (row != null) ? (int) Math.round(row.getHeightInPoints() * Units.PIXEL_DPI / Units.POINT_DPI) : drheight;

		String name = null;
		String objectType = null;
		String checked = null;

		XmlCursor xmlcursor = null;
		if (sheet.getCTWorksheet().getLegacyDrawing() != null) {
			String legacyDrawingId = sheet.getCTWorksheet().getLegacyDrawing().getId();
			POIXMLDocumentPart part = sheet.getRelationById(legacyDrawingId);
			XmlObject xmlDrawing = XmlObject.Factory.parse(part.getPackagePart().getInputStream());
			xmlcursor = xmlDrawing.newCursor();
			QName qnameClientData = new QName("urn:schemas-microsoft-com:office:excel", "ClientData", "x");
			QName qnameAnchor = new QName("urn:schemas-microsoft-com:office:excel", "Anchor", "x");
			boolean controlFound = false;
			while (xmlcursor.hasNextToken()) {
				XmlCursor.TokenType tokentype = xmlcursor.toNextToken();
				if (tokentype.isStart()) {
					if (qnameClientData.equals(xmlcursor.getName())) {
						controlFound = true;
						XmlObject clientdata = xmlcursor.getObject();
						XmlObject[] xmlchecked = clientdata.selectPath("declare namespace x='urn:schemas-microsoft-com:office:excel' x:Checked");
						if (xmlchecked.length > 0) {
							checked = "Checked";
						} else {
							checked = "Not checked";
						}
						while (xmlcursor.hasNextToken()) {
							tokentype = xmlcursor.toNextToken();
							if (tokentype.isAttr()) {
								if (new QName("ObjectType").equals(xmlcursor.getName())) {
									objectType = xmlcursor.getTextValue();
									name = objectType + " in row " + (r + 1);
								}
							} else {
								break;
							}
						}
					} else if (qnameAnchor.equals(xmlcursor.getName()) && controlFound) {
						controlFound = false;
						String anchorContent = xmlcursor.getTextValue().trim();
						String[] anchorparts = anchorContent.split(",");
						int fromCol = Integer.parseInt(anchorparts[0].trim());
						int fromColDx = Integer.parseInt(anchorparts[1].trim());
						int fromRow = Integer.parseInt(anchorparts[2].trim());
						int fromRowDy = Integer.parseInt(anchorparts[3].trim());
						int toCol = Integer.parseInt(anchorparts[4].trim());
						int toColDx = Integer.parseInt(anchorparts[5].trim());
						int toRow = Integer.parseInt(anchorparts[6].trim());
						int toRowDy = Integer.parseInt(anchorparts[7].trim());

						if (fromCol == c && (fromRow == r || (fromRow == r - 1 && fromRowDy > rheightbefore / 2f))
								&& (toRow == r || (toRow == r + 1 && toRowDy < rheightafter / 2f))) {
							break;
						}
					}
				}
			}
		}

		if (xmlcursor != null && xmlcursor.hasNextToken())
			return new Control(name, objectType, checked, r, c);

		return new Control("Not found", "unknown", "undefined", r, c);
	}

	public static void main(String[] args) throws Exception {
		ReadExcelXSSFControls o = new ReadExcelXSSFControls();
	}

	private class Control {
		private String name;
		private String objectType;
		private String checked;
		private int row;
		private int col;

		public Control(String name, String objectType, String checked, int row, int col) {
			this.name = name;
			this.objectType = objectType;
			this.checked = checked;
			this.row = row;
			this.col = col;
		}

		public String getName() {
			return this.name;
		}

		public String getObjectType() {
			return this.objectType;
		}

		public String getChecked() {
			return this.checked;
		}

		public int getRow() {
			return this.row;
		}

		public int getCol() {
			return this.col;
		}

		public String toString() {
			return this.name + ":r/c:" + row + "/" + col + ":" + this.checked;
		}
	}
}
